const { QueryTypes } = require('sequelize');

const querystring = require('querystring');
const count = 5; // 按下载量排序时，默认返回5条

module.exports = {
    typeListDesign: async (ctx) => {
        let body;
        try {
            body = ctx.sequelize.query(
                `SELECT name from design_folders order by orders;`,
                {
                    // 用于记录查询的函数(或false)
                    // 将调用发送到服务器的每个SQL查询.
                    // logging: console.log,
                    timestamps: false,
                    // 如果plain为true,则sequelize将仅返回结果集的第一条记录.
                    // 如果是false,它将返回所有记录.
                    plain: false,

                    // 如果你没有查询的模型定义,请将此项设置为true.
                    raw: false,

                    // 你正在执行的查询类型. 查询类型会影响结果在传回之前的格式.
                    type: QueryTypes.SELECT,
                }
            );
            // ctx.sequelize.close();
        } catch (error) {
            body = '';
        }
        return body;
    },
    record: async (ctx) => {
        // console.log('ctx', ctx);
        const queryObj = querystring.parse(ctx.url.split('?')[1]);
        const folder = queryObj.folder || '';
        const name = queryObj.name || '';
        const type = queryObj.type || '1'; // type 1:浏览数， 2：下载数
        let sql;
        if (type == 1) {
            sql = `UPDATE design_records SET view_count = view_count+1 WHERE folder = '${folder}' and file_name = '${name}' and is_delete = 0`;
        } else {
            sql = `UPDATE design_records SET download_count = download_count+1 WHERE folder = '${folder}' and file_name = '${name}' and is_delete = 0`;
        }
        let body;
        try {
            body = ctx.sequelize.query(sql, {timestamps: false, plain: false, raw: false, type: QueryTypes.UPDATE,});
        } catch (error) {
            body = '';
        }
        return body;
    },
    getFileList: async (ctx) => {
        const queryObj = querystring.parse(ctx.url.split('?')[1]);
        const folder = queryObj.folder || '';
        const sk = queryObj.sk || '';
        const order = queryObj.order || '0'; // order: 0:默认顺序，1：下载量排序
        let sql;
        if (order == 0) {
            sql = `SELECT * FROM design_records WHERE folder = '${folder}' AND file_name like '%${sk}%' AND is_delete = 0`;
        } else {
            sql = `SELECT * FROM design_records WHERE folder = '${folder}' AND file_name like '%${sk}%' AND is_delete = 0 ORDER BY download_count DESC limit ${count}`;
        }
        let body;
        try {
            body = ctx.sequelize.query(sql, {timestamps: false, plain: false, raw: false, type: QueryTypes.SELECT,});
        } catch (error) {
            body = '';
        }
        return body;
    },
    getDownloadCount: async (ctx) => {
        const queryObj = querystring.parse(ctx.url.split('?')[1]);
        const folder = queryObj.folder || '';
        let body;
        if (folder === '全部') {
            sql = `SELECT SUM(download_count) AS download_counts, SUM(view_count) AS view_counts FROM design_records WHERE is_delete = 0`;
        } else {
            sql = `SELECT SUM(download_count) AS download_counts, SUM(view_count) AS view_counts FROM design_records WHERE folder = '${folder}' AND is_delete = 0`;
        }
        try {
            body = ctx.sequelize.query(sql, 
                {timestamps: false, plain: false, raw: false, type: QueryTypes.SELECT,});
        } catch (error) {
            body = '';
        }
        return body;
    }
};
